package com.slipper.common.service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.slipper.common.dao.CommonDao;
import com.slipper.common.exception.CommonException;
import com.slipper.common.transaction.ReadTransactional;
import com.slipper.common.transaction.WriteTransactional;
import org.apache.ibatis.session.ResultHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
@ReadTransactional
public class CommonService {
    private static final Logger logger = LoggerFactory.getLogger(CommonService.class);

    @Resource
    private CommonDao CommonDao;
    @Resource
    private DataSource dataSource;

    public List<Map<String, Object>> queryTables() {
        return CommonDao.queryTables();
    }

    public int queryTotalByTableName(String tableName) {
        return CommonDao.queryTotalByTableName(tableName);
    }

    public Map<String, String> queryTable(String tableName) {
        return CommonDao.queryTable(tableName);
    }

    public List<Map<String, Object>> queryColumns(String tableName) {
        return CommonDao.queryColumns(tableName);
    }

    public List<Map<String, Object>> queryColumnsBySql(String sql, Map<String, Object> paramMap) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            List<Object> list = new ArrayList<>();
            Pattern pattern = Pattern.compile("#\\{[a-zA-Z0-9_]*}");
            Matcher matcher = pattern.matcher(sql);
            while (matcher.find()) {
                String key = matcher.group().substring(2, matcher.group().length() - 1);
                list.add(paramMap.get(key));
                sql = sql.replace(matcher.group(), "?");
            }
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql);
            for (int i=0; i<list.size(); i++) {
                statement.setObject(i + 1, list.get(i));
            }
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            List<Map<String, Object>> dataList = new ArrayList<>();
            for (int i=1; i<=metaData.getColumnCount(); i++) {
                Map<String, Object> data = new HashMap<>();
                data.put("columnName", metaData.getColumnName(i));
                data.put("dataType", metaData.getColumnType(i));
                data.put("columnOrdinal", i);
                data.put("columnSize", metaData.getColumnDisplaySize(i));
                data.put("columnPrecision", metaData.getPrecision(i));
                data.put("columnScale", metaData.getScale(i));
                data.put("columnComment", metaData.getColumnLabel(i));
                data.put("columnKey", null);
                data.put("extra", null);
                data.put("columnDefault", null);
                dataList.add(data);
            }
            return dataList;
        } catch (Exception e) {
            logger.error("获取sql数据meta data出错！", e);
            throw new CommonException("获取sql数据meta data出错！");
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (Exception e) {
                    logger.error("关闭ResultSet出错！", e);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                    logger.error("关闭Statement出错！", e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (Exception e) {
                    logger.error("关闭Connection出错！", e);
                }
            }
        }
    }

    public Map<String, Object> queryOne(Map<String, Object> paramMap) {
        return CommonDao.queryOne(paramMap);
    }

    public List<Map<String, Object>> queryList(Map<String, Object> paramMap) {
        return CommonDao.queryList(paramMap);
    }

    public List<Map<String, Object>> queryListWithHandler(Map<String, Object> paramMap, ResultHandler<?> handler) {
        return CommonDao.queryListWithHandler(paramMap, handler);
    }

    public int queryTotal(Map<String, Object> paramMap) {
        return CommonDao.queryTotal(paramMap);
    }

    @WriteTransactional
    public int execute(Map<String, Object> paramMap) {
        return CommonDao.execute(paramMap);
    }

    public PageInfo<Map<String, Object>> queryPage(int pageNumber, int pageSize, Map<String, Object> paramMap) {
        PageHelper.startPage(pageNumber, pageSize);
        return new PageInfo<>(CommonDao.queryList(paramMap));
    }
}
